Programming Microsoft Visual Basic .NET/Programming Microsoft Visual Basic .NET (Microsoft Press)(X08-78517)(2002).bin/20 adonet connected/adonet connected/connectedadonetform.vb

Text File | 2002-03-16 | 31.5 KB | 833 lines

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class ConnectedAdonetForm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents btnOpen As System.Windows.Forms.Button
    Friend WithEvents btnAsynchronous As System.Windows.Forms.Button
    Friend WithEvents lblStatus As System.Windows.Forms.Label
    Friend WithEvents btnNestedTrans As System.Windows.Forms.Button
    Friend WithEvents btnNamedTrans As System.Windows.Forms.Button
    Friend WithEvents btnActionCommand As System.Windows.Forms.Button
    Friend WithEvents btnQuery As System.Windows.Forms.Button
    Friend WithEvents txtOut As System.Windows.Forms.TextBox
    Friend WithEvents btnExecuteScalar As System.Windows.Forms.Button
    Friend WithEvents btnExecuteXmlReader As System.Windows.Forms.Button
    Friend WithEvents btnGetValues As System.Windows.Forms.Button
    Friend WithEvents btnMultipleResultsets As System.Windows.Forms.Button
    Friend WithEvents btnErrors As System.Windows.Forms.Button
    Friend WithEvents btnStoredProc As System.Windows.Forms.Button
    Friend WithEvents btnParamCommand As System.Windows.Forms.Button

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.Container

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents btnDeriveParams As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnMultipleResultsets = New System.Windows.Forms.Button()
        Me.btnActionCommand = New System.Windows.Forms.Button()
        Me.txtOut = New System.Windows.Forms.TextBox()
        Me.btnStoredProc = New System.Windows.Forms.Button()
        Me.btnQuery = New System.Windows.Forms.Button()
        Me.btnNestedTrans = New System.Windows.Forms.Button()
        Me.btnExecuteScalar = New System.Windows.Forms.Button()
        Me.btnExecuteXmlReader = New System.Windows.Forms.Button()
        Me.btnErrors = New System.Windows.Forms.Button()
        Me.lblStatus = New System.Windows.Forms.Label()
        Me.btnOpen = New System.Windows.Forms.Button()
        Me.btnAsynchronous = New System.Windows.Forms.Button()
        Me.btnParamCommand = New System.Windows.Forms.Button()
        Me.btnNamedTrans = New System.Windows.Forms.Button()
        Me.btnGetValues = New System.Windows.Forms.Button()
        Me.btnDeriveParams = New System.Windows.Forms.Button()
        Me.SuspendLayout()
        '
        'btnMultipleResultsets
        '
        Me.btnMultipleResultsets.Location = New System.Drawing.Point(160, 304)
        Me.btnMultipleResultsets.Name = "btnMultipleResultsets"
        Me.btnMultipleResultsets.Size = New System.Drawing.Size(128, 40)
        Me.btnMultipleResultsets.TabIndex = 0
        Me.btnMultipleResultsets.Text = "Multiple Resultsets"
        '
        'btnActionCommand
        '
        Me.btnActionCommand.Location = New System.Drawing.Point(24, 256)
        Me.btnActionCommand.Name = "btnActionCommand"
        Me.btnActionCommand.Size = New System.Drawing.Size(128, 40)
        Me.btnActionCommand.TabIndex = 0
        Me.btnActionCommand.Text = "Action Command"
        '
        'txtOut
        '
        Me.txtOut.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right)
        Me.txtOut.Location = New System.Drawing.Point(312, 16)
        Me.txtOut.Multiline = True
        Me.txtOut.Name = "txtOut"
        Me.txtOut.ScrollBars = System.Windows.Forms.ScrollBars.Both
        Me.txtOut.Size = New System.Drawing.Size(320, 328)
        Me.txtOut.TabIndex = 2
        Me.txtOut.Text = ""
        Me.txtOut.WordWrap = False
        '
        'btnStoredProc
        '
        Me.btnStoredProc.Location = New System.Drawing.Point(160, 208)
        Me.btnStoredProc.Name = "btnStoredProc"
        Me.btnStoredProc.Size = New System.Drawing.Size(128, 40)
        Me.btnStoredProc.TabIndex = 0
        Me.btnStoredProc.Text = "Stored Procedures"
        '
        'btnQuery
        '
        Me.btnQuery.Location = New System.Drawing.Point(24, 304)
        Me.btnQuery.Name = "btnQuery"
        Me.btnQuery.Size = New System.Drawing.Size(128, 40)
        Me.btnQuery.TabIndex = 0
        Me.btnQuery.Text = "Query Command"
        '
        'btnNestedTrans
        '
        Me.btnNestedTrans.Location = New System.Drawing.Point(24, 160)
        Me.btnNestedTrans.Name = "btnNestedTrans"
        Me.btnNestedTrans.Size = New System.Drawing.Size(128, 40)
        Me.btnNestedTrans.TabIndex = 0
        Me.btnNestedTrans.Text = "Nested Transactions"
        '
        'btnExecuteScalar
        '
        Me.btnExecuteScalar.Location = New System.Drawing.Point(160, 64)
        Me.btnExecuteScalar.Name = "btnExecuteScalar"
        Me.btnExecuteScalar.Size = New System.Drawing.Size(128, 40)
        Me.btnExecuteScalar.TabIndex = 0
        Me.btnExecuteScalar.Text = "ExecuteScalar"
        '
        'btnExecuteXmlReader
        '
        Me.btnExecuteXmlReader.Location = New System.Drawing.Point(160, 112)
        Me.btnExecuteXmlReader.Name = "btnExecuteXmlReader"
        Me.btnExecuteXmlReader.Size = New System.Drawing.Size(128, 40)
        Me.btnExecuteXmlReader.TabIndex = 0
        Me.btnExecuteXmlReader.Text = "ExecuteXmlReader"
        '
        'btnErrors
        '
        Me.btnErrors.Location = New System.Drawing.Point(24, 64)
        Me.btnErrors.Name = "btnErrors"
        Me.btnErrors.Size = New System.Drawing.Size(128, 40)
        Me.btnErrors.TabIndex = 0
        Me.btnErrors.Text = "Errors collection"
        '
        'lblStatus
        '
        Me.lblStatus.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
        Me.lblStatus.Dock = System.Windows.Forms.DockStyle.Bottom
        Me.lblStatus.Location = New System.Drawing.Point(0, 357)
        Me.lblStatus.Name = "lblStatus"
        Me.lblStatus.Size = New System.Drawing.Size(648, 24)
        Me.lblStatus.TabIndex = 1
        '
        'btnOpen
        '
        Me.btnOpen.Location = New System.Drawing.Point(24, 16)
        Me.btnOpen.Name = "btnOpen"
        Me.btnOpen.Size = New System.Drawing.Size(128, 40)
        Me.btnOpen.TabIndex = 0
        Me.btnOpen.Text = "Open the connection"
        '
        'btnAsynchronous
        '
        Me.btnAsynchronous.Location = New System.Drawing.Point(24, 112)
        Me.btnAsynchronous.Name = "btnAsynchronous"
        Me.btnAsynchronous.Size = New System.Drawing.Size(128, 40)
        Me.btnAsynchronous.TabIndex = 0
        Me.btnAsynchronous.Text = "Asynchronous Open"
        '
        'btnParamCommand
        '
        Me.btnParamCommand.Location = New System.Drawing.Point(160, 160)
        Me.btnParamCommand.Name = "btnParamCommand"
        Me.btnParamCommand.Size = New System.Drawing.Size(128, 40)
        Me.btnParamCommand.TabIndex = 0
        Me.btnParamCommand.Text = "Parameterized Command"
        '
        'btnNamedTrans
        '
        Me.btnNamedTrans.Location = New System.Drawing.Point(24, 208)
        Me.btnNamedTrans.Name = "btnNamedTrans"
        Me.btnNamedTrans.Size = New System.Drawing.Size(128, 40)
        Me.btnNamedTrans.TabIndex = 0
        Me.btnNamedTrans.Text = "Named Transactions"
        '
        'btnGetValues
        '
        Me.btnGetValues.Location = New System.Drawing.Point(160, 16)
        Me.btnGetValues.Name = "btnGetValues"
        Me.btnGetValues.Size = New System.Drawing.Size(128, 40)
        Me.btnGetValues.TabIndex = 0
        Me.btnGetValues.Text = "the GetValues method"
        '
        'btnDeriveParams
        '
        Me.btnDeriveParams.Location = New System.Drawing.Point(160, 256)
        Me.btnDeriveParams.Name = "btnDeriveParams"
        Me.btnDeriveParams.Size = New System.Drawing.Size(128, 40)
        Me.btnDeriveParams.TabIndex = 3
        Me.btnDeriveParams.Text = "DeriveParameters method"
        '
        'ConnectedAdonetForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(7, 17)
        Me.ClientSize = New System.Drawing.Size(648, 381)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnDeriveParams, Me.btnParamCommand, Me.btnStoredProc, Me.btnErrors, Me.btnMultipleResultsets, Me.btnGetValues, Me.btnExecuteXmlReader, Me.btnExecuteScalar, Me.txtOut, Me.btnQuery, Me.btnActionCommand, Me.btnNamedTrans, Me.btnNestedTrans, Me.lblStatus, Me.btnAsynchronous, Me.btnOpen})
        Me.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Name = "ConnectedAdonetForm"
        Me.Text = "ADO.NET Demo"
        Me.ResumeLayout(False)

    End Sub

#End Region

    ' ensure that only one of these three constants is true
#Const OLEDBBIBLIO = -1
#Const OLEDBPUBS = 0
#Const SQLPUBS = 0

    ' open the connection - must be the first step

    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString )
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
#End If
        AddHandler cn.StateChange, AddressOf cn_StateChange

        Try
            cn.Open()
            ' process the data here.
            ' ...
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            ' Close the connection whather it happens.
            cn.Close()
        End Try
    End Sub

    ' this event fires when the connection is opened or closed

    Private Sub cn_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs)
        ' Show the status of the connection in a Label control.
        If (e.CurrentState And ConnectionState.Open) <> 0 Then
            lblStatus.Text = "The connection has been opened"
        ElseIf e.CurrentState = ConnectionState.Closed Then
            lblStatus.Text = "The connection has been closed"
        End If
    End Sub

    ' list errors

    Private Sub btnErrors_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnErrors.Click

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim cmd As New OleDbCommand("UPDATE xyz SET id=1", cn)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString )
        Dim cmd As New OleDbCommand("UPDATE xyz SET id=1", cn)
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand("UPDATE xyz SET id=1", cn)
#End If

        Try
            cn.Open()
            ' Run a query that references a table that doesn't exist.
            cmd.ExecuteNonQuery()

#If OLEDBBIBLIO Or OLEDBPUBS Then
        Catch ex As OleDbException
            ' An OleDbException has occurred - display details
            Dim i As Integer, msg As String
            For i = 0 To ex.errors.Count - 1
                Dim er As OleDbError = ex.Errors(i)
                msg = "Message = " & er.Message & ControlChars.CrLf
                msg &= "Source = " & er.Source & ControlChars.CrLf
                msg &= "NativeError = " & er.NativeError & ControlChars.CrLf
                msg &= "SQLState = " & er.SQLState & ControlChars.CrLf
            Next
            txtOut.Text = msg
#Else
        Catch ex As SqlException
            ' An SqlException has occurred - display details
            Dim i As Integer, msg As String
            For i = 0 To ex.errors.Count - 1
                Dim er As SqlError = ex.Errors(i)
                msg = "Message = " & er.Message & ControlChars.CrLf
                msg &= "Source = " & er.Source & ControlChars.CrLf
                msg &= "State = " & er.State & ControlChars.CrLf
                msg &= "Server = " & er.Server & ControlChars.CrLf
            Next
            txtOut.Text = msg
#End If

        Catch ex As Exception
            ' A generic expection has occurred.
            txtOut.Text = ex.Message
        End Try

    End Sub

    Delegate Sub OpenMethod()

    ' demonstrates asynchronous calls

    Private Sub btnAsynchronous_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAsynchronous.Click

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString )
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
#End If

        ' Create a delegate that points to it.
        Dim asyncOpen As New OpenMethod(AddressOf cn.Open)
        ' Open it asynchronously - pass the delegate as the cookie.
        Dim ar As IAsyncResult = asyncOpen.BeginInvoke(AddressOf OpenComplete, asyncOpen)
        ' Do something else here.
        ' ...
        lblStatus.Text = "Waiting ..."
    End Sub

    ' this method is called when the Open operation is completed

    Sub OpenComplete(ByVal ar As IAsyncResult)
        ' retrieve a reference to the delegate.
        Dim asyncOpen As OpenMethod = CType(ar.AsyncState, OpenMethod)

        Try
            ' Complete the operation.
            asyncOpen.EndInvoke(ar)
            lblStatus.Text = "The connection has been opened"
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    ' demonstrates nested transactions (Access only)

    Private Sub btnNestedTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNestedTrans.Click
        ' This works only with Access databases.
        Dim cn As New OleDbConnection(BiblioConnString)
        Try
            cn.Open()
            ' Open the first transaction
            Dim tr As OleDbTransaction = cn.BeginTransaction(IsolationLevel.ReadCommitted)
            ' Open a nested transaction
            Dim tr2 As OleDbTransaction = tr.Begin(IsolationLevel.ReadUncommitted)
            ' ...
            ' Rollback the inner transaction.
            tr2.Rollback()

            ' Commit the outer transaction.
            tr.Commit()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            cn.Close()
        End Try
    End Sub

    ' demonstrates named SQL Server transactions

    Private Sub btnNamedTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNamedTrans.Click
        ' This only works with the SQL Server provider
        Dim cn As New SqlConnection(SqlPubsConnString)

        Try
            cn.Open()

            ' Open a named transaction
            Dim tr As SqlTransaction = cn.BeginTransaction(IsolationLevel.ReadCommitted, "MainTran")
            ' create a savepoint
            tr.Save("EndOfFirstPart")

            ' ...
            ' Rollback the inner transaction.
            tr.Rollback("EndOfFirstPart")

            ' Commit the outer transaction.
            tr.Commit()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            cn.Close()
        End Try
    End Sub

    ' perform an action SQL command

    Private Sub btnActionCommand_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActionCommand.Click

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim tr As OleDbTransaction
        Dim sql As String = "INSERT INTO Authors (Author, [Year Born]) VALUES ('Joe Doe', 1955)"
        Dim cmd As OleDbCommand
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString )
        Dim tr As OleDbTransaction
        Dim cmd As OleDbCommand
        Dim sql as String = "INSERT Authors (au_lname, au_fname, phone, address, city, state, zip, contract) " _
            & "VALUES ('Doe', 'Joe', '123-123-1234', '9999 Street', 'San Francisco', 'CA', -1)"
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim tr As SqlTransaction
        Dim cmd As SqlCommand
        Dim sql as String = "INSERT Authors (au_lname, au_fname, phone, address, city, state, zip, contract) " _
            & "VALUES ('Doe', 'Joe', '123-123-1234', '9999 Street', 'San Francisco', 'CA', -1)"
#End If
        Try
            ' Open a connection.
            cn.Open()
            ' Open a transaction.
            tr = cn.BeginTransaction()

            ' Create an action command on that connection.
            cmd = cn.CreateCommand()
            cmd.CommandText = sql
            cmd.Transaction = tr

            ' Run the query, get the number of affected records.
            Dim records As Integer = cmd.ExecuteNonQuery()
            Debug.WriteLine(records)                      ' => 1

            ' Commit the transaction
            tr.Commit()

        Catch ex As OleDbException
            Debug.WriteLine(ex.Message)
        Finally
            ' Close the connection.
            cn.Close()
        End Try
    End Sub

    ' perform a query SQL command

    Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuery.Click

        ' Define the command to read the Publishers table
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#ElseIf OLEDBPUBS Then
        DIm cn as New OleDbConnection(OledbPubsConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#ElseIf SQLPUBS Then
        DIm cn as New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
#End If

        Try
            ' Open a connection.
            cn.Open()
            dr = cmd.ExecuteReader()

            ' Display all fields
            Do While dr.Read
                Dim res As String = ""
                Dim i As Integer
                ' Iterate over all fields.
                For i = 0 To dr.FieldCount - 1
                    ' Insert a comma if necessary.
                    If res.Length > 0 Then res &= ", "
                    ' Append field name and value.
                    res &= dr.GetName(i) & "="
                    If dr.IsDBNull(i) Then
                        res &= "<NULL>"
                    Else
                        res &= dr.GetValue(i).ToString
                    End If
                Next
                ' Append to the result textbox.
                txtOut.AppendText(res & ControlChars.CrLf)
            Loop
            dr.Close()

        Catch ex As OleDbException
            Debug.WriteLine(ex.Message)
        Finally
            ' Close the connection.
            cn.Close()
        End Try

    End Sub

    ' display values from a query

    Private Sub btnGetValues_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetValues.Click

        ' Define the command to read the Publishers table
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#ElseIf OLEDBPUBS Then
        DIm cn as New OleDbConnection(OledbPubsConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#ElseIf SQLPUBS Then
        DIm cn as New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
#End If

        Try
            ' Open a connection.
            cn.Open()
            ' Read data into the DataReader
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            ' Build the array of all fields.
            Dim fldNames(dr.FieldCount - 1) As String
            Dim i As Integer
            For i = 0 To dr.FieldCount - 1
                fldNames(i) = dr.GetName(i)
            Next

            ' Display all fields
            Do While dr.Read
                Dim res As New System.Text.StringBuilder(256)
                ' Get all the values in one shot.
                Dim values(dr.FieldCount - 1) As Object
                dr.GetValues(values)

                ' Iterate over all fields.
                For i = 0 To dr.FieldCount - 1
                    ' Insert a comma if necessary.
                    If res.Length > 0 Then res.Append(", ")
                    ' Append field name and equal sign.
                    res.Append(fldNames(i))
                    res.Append("=")
                    ' Append the field value, or <NULL>.
                    If IsDBNull(values(i)) Then
                        res.Append("<NULL>")
                    Else
                        res.Append(values(i).ToString)
                    End If
                Next
                ' Append to the result textbox.
                res.Append(ControlChars.CrLf)
                txtOut.AppendText(res.ToString)
            Loop

        Catch ex As OleDbException
            Debug.WriteLine(ex.Message)
        Finally
            ' Close the DataReader and the Connection
            dr.Close()
        End Try
    End Sub

    ' demonstrates the ExecuteScalar method

    Private Sub btnExecuteScalar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteScalar.Click
        ' Define the command to read a single scalar value

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim sql As String = "SELECT Name FROM Publishers WHERE PubID=1"
        Dim cmd As New OleDbCommand(sql, cn)
#ElseIf OLEDBPUBS Then
        DIm cn as New OleDbConnection(OledbPubsConnString)
        Dim sql As String = "SELECT Name FROM Publishers WHERE pub_ID=1"
        Dim cmd As New OleDbCommand(sql, cn)
#ElseIf SQLPUBS Then
        DIm cn as New SqlConnection(SqlPubsConnString)
        Dim sql As String = "SELECT Name FROM Publishers WHERE pub_id=1"
        Dim cmd As New SqlCommand(sql, cn)
#End If

        Try
            ' Open a connection.
            cn.Open()

            ' Read the value.
            txtOut.Text = cmd.ExecuteScalar().ToString
        Catch ex As OleDbException
            Debug.WriteLine(ex.Message)

        Finally
            ' Close the connection.
            cn.Close()
        End Try
    End Sub

    ' demonstrates the ExecuteXmlReader method (SQL Server only)

    Private Sub btnExecuteXmlReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteXmlReader.Click
        ' Open a connection to SQL Server 2000.
        Dim cn As New SqlConnection(SqlPubsConnString)
        cn.Open()

        ' Prepare a FOR XML command.
        Dim cmd As New SqlCommand("SELECT pub_name FROM Publishers FOR XML AUTO, ELEMENTS", cn)
        ' Create the XmlReader.
        Dim reader As System.Xml.XmlReader = cmd.ExecuteXmlReader()
        ' Display XML data.
        Do While reader.Read
            txtOut.AppendText(reader.Value & ControlChars.CrLf)
        Loop
        ' Close the XmlReader and the connection.
        reader.Close()
        cn.Close()
    End Sub

    ' call a parameterized command

    Private Sub btnParamCommand_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParamCommand.Click

        ' Prepare a command on that connection
#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim sql As String = "SELECT * FROM Publishers WHERE PubID=?"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        Dim fldName As String = "Name"
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString )
        Dim sql As String = "SELECT * FROM Publishers WHERE pub_id=?"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr as OleDbDataReader
        Dim fldName as String = "pub_name"
#Else
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim sql As String = "SELECT * FROM Publishers WHERE PubID=@p1"
        Dim cmd As New SqlCommand(sql, cn)
        dim dr as SqlDataReader
        Dim fldName as String = "pub_name"
#End If

        ' open the connection
        cn.Open()

        ' Define the first (and only) parameter and assign its value.
        cmd.Parameters.Add("PubID", 1)

        ' Read the result.
        dr = cmd.ExecuteReader()
        ' No need to loop, because we know there is only one row.
        dr.Read()
        txtOut.Text = dr(fldName).ToString & ControlChars.CrLf
        dr.Close()

        ' Change the parameter's value and re-execute the query.
        cmd.Parameters(0).Value = 10
        dr = cmd.ExecuteReader
        dr.Read()
        txtOut.AppendText(dr(fldName).ToString & ControlChars.CrLf)

        dr.Close()
        cn.Close()
    End Sub

    ' call a stored procedure
    ' IMPORTANT: requires that the Pubs database contains a stored proc
    ' named ByRoyalty2 - read book for directions about creating this SP

    Private Sub btnStoredProc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStoredProc.Click
        ' This only works with SQL Server

        ' This is the name of the stored procedure.
        ' (Note that you must create this procedure, which isn't in PUBS)
        Dim sql As String = "byroyalty2"

#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#Else
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand(sql, cn)
        dim dr as SqlDataReader
#End If
        ' Open the connection.
        cn.Open()

        cmd.CommandType = CommandType.StoredProcedure

        ' Define the return value parameter.
        cmd.Parameters.Add("@numtitles", OleDbType.Integer)
        cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
        ' Define the first (input) parameter and assign its value.
        cmd.Parameters.Add("@percentage", 100)

        ' Define the second (output) parameter and set it direction.
        ' (A better method for setting the direction and other properties.)
        With cmd.Parameters.Add("@avgprice", SqlDbType.Float)
            .Direction = ParameterDirection.Output
        End With

        ' Read the result.
        dr = cmd.ExecuteReader()
        Do While dr.Read
            txtOut.AppendText(dr(0).ToString & ControlChars.CrLf)
        Loop
        dr.Close()

        ' After you close the DataReader you can read the return value and output argument.
        txtOut.AppendText("# of titles = " & cmd.Parameters("@numtitles").Value.ToString & ControlChars.CrLf)
        txtOut.AppendText("Average price = " & cmd.Parameters("@avgprice").Value.ToString)

        cn.Close()
    End Sub

    ' demonstrate the DeriveParameters method
    ' IMPORTANT: requires that the Pubs database contains a stored proc
    ' named ByRoyalty2 - read book for directions about creating this SP

    Private Sub btnDeriveParams_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeriveParams.Click
        ' This only works with SQL Server

        ' This is the name of the stored procedure.
        ' (Note that you must create this procedure, which isn't in PUBS)
        Dim sql As String = "byroyalty2"

#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#Else
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand(sql, cn)
        dim dr as SqlDataReader
#End If

        ' Open the connection.
        cn.Open()
        cmd.CommandType = CommandType.StoredProcedure

        ' Populate the Parameters collection with a trip to the server.
#If OLEDBBIBLIO Or OLEDBPUBS Then
        OleDbCommandBuilder.DeriveParameters(cmd)
        Dim par As OleDbParameter
#Else
        SqlCommandBuilder.DeriveParameters(cmd)
        Dim par As SqlParameter
#End If

        ' Display parameters found.
        Dim msg As String
        For Each par In cmd.Parameters
            msg &= par.ParameterName & " " & par.DbType.ToString & " (" & par.Direction.ToString & ")" & ControlChars.CrLf
        Next
        MessageBox.Show(msg, "Parameters found", MessageBoxButtons.OK, MessageBoxIcon.Information)

#If OLEDBBIBLIO Or OLEDBPUBS Then
        ' adjust param names to comply with sql server names.
        For Each par In cmd.Parameters
            par.ParameterName = "@" & par.ParameterName
        Next
#End If

        ' set the value of the (only) input parameter
        cmd.Parameters("@percentage").Value = 100
        'pass a dummy value for the output parameter (incorrectly retrieved as input/output)
        cmd.Parameters("@avgprice").Value = 0

        ' Read the result.
        dr = cmd.ExecuteReader()
        Do While dr.Read
            txtOut.AppendText(dr(0).ToString & ControlChars.CrLf)
        Loop
        dr.Close()

        ' After you close the DataReader you can read the return value and output argument.
        txtOut.AppendText("# of titles = " & cmd.Parameters("@RETURN_VALUE").Value.ToString & ControlChars.CrLf)
        txtOut.AppendText("Average price = " & cmd.Parameters("@avgprice").Value.ToString)

        cn.Close()
    End Sub

    ' read multiple resultsets (SQL Server only)

    Private Sub btnMultipleResultsets_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMultipleResultsets.Click
        Dim sql As String = "SELECT pub_name FROM Publishers;SELECT Title FROM titles;"

        ' Open a connection to Pubs databse on SQL Server .
#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
#Else
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim cmd As New SqlCommand(sql, cn)
        dim dr as SqlDataReader
#End If

        cn.Open()

        ' Define an SQL statement with multiple queries.
        dr = cmd.ExecuteReader()
        Dim resCount As Integer

        Do
            resCount += 1
            txtOut.AppendText("RESULTSET #" & resCount.ToString)
            txtOut.AppendText(ControlChars.CrLf)

            Do While dr.Read
                txtOut.AppendText(dr(0).ToString)
                txtOut.AppendText(ControlChars.CrLf)
            Loop
            txtOut.AppendText(ControlChars.CrLf)
        Loop While dr.NextResult
        dr.Close()
        cn.Close()
    End Sub

End Class